﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Data.OracleClient;
using TracNghiem.Models;
namespace TracNghiem.Areas.Admin.Models
{
    public class BaiThiModel
    {
        public string id { get; set; }
        public string user_id { get; set; }
        public string lan_thi { get; set; }
        public string de_thi_id { get; set; }
        public string cau_tl_id { get; set; }
        public string user_status { get; set; }
        public string ngay { get; set; }
        
        public string user_name { get; set; }
        public string ho_ten { get; set; }
        public string ngay_thi { get; set; }
        public string cau_dung { get; set; }
        public string tong_so_cau { get; set; }
        public string don_vi { get; set; }
        public string ten_de_thi { get; set; }

        public void addBai_Thi(BaiThiModel baithi)
        {
            string sql = "INSERT INTO bai_thi";
            sql += " select nvl((select max(id)+1 from bai_thi ),1)," + baithi.user_id + ","+baithi.lan_thi+",";
            sql += baithi.de_thi_id + "," + baithi.cau_tl_id + "," + baithi.user_status + ",to_date('" + baithi.ngay + "','dd/mm/yyyy') from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public string getLan_thi(string de_thi_id,string user_id)
        {
            string sql = "SELECT nvl((select max(lan_thi)+1 from bai_thi where user_id=" + user_id + " and de_thi_id=" + de_thi_id + " ),1) from dual ";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            string lan_thi="";
            while (odr.Read())
            {
                lan_thi = odr[0].ToString();
            }
            General.conn_global.Close();
            return lan_thi;

        }
        public List<BaiThiModel> getReport_General()
        {
            List<BaiThiModel> bt = new List<BaiThiModel>();
            string sql = "select a.*,b.* from ";
            sql += " (SELECT * FROM bc_ketqua a) a,(select * from de_thi)b where a.de_thi_id=b.id  ";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngayIndex = 0;
            while (odr.Read())
            {
                ngayIndex = odr.GetOrdinal("ngay");
                bt.Add(new BaiThiModel
                {
                    user_id = odr["user_id"].ToString(),
                    don_vi = odr["don_vi"].ToString(),
                    user_name = odr["user_name"].ToString(),
                    ho_ten = odr["ho_ten"].ToString(),
                    lan_thi = odr["lan_thi"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    ngay_thi = odr.IsDBNull(ngayIndex) ? null : odr.GetDateTime(ngayIndex).ToString("dd/MM/yyyy"),
                    cau_dung = odr["dung"].ToString(),
                    tong_so_cau = odr["tong"].ToString(),
                    ten_de_thi = odr["name"].ToString()
                });
            }
            General.conn_global.Close();
            return bt;
        }

        public List<BaiThiModel> FindReport_General(string str)
        {
            List<BaiThiModel> bt = new List<BaiThiModel>();
            //string sql = "select * from bc_ketqua and " + str;
            string sql = "select a.*,b.* from ";
            sql += " (SELECT * FROM bc_ketqua a) a,(select * from de_thi)b where a.de_thi_id=b.id  "+str;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngayIndex = 0;
            while (odr.Read())
            {
                ngayIndex = odr.GetOrdinal("ngay");
                bt.Add(new BaiThiModel
                {
                    user_id = odr["user_id"].ToString(),
                    don_vi = odr["don_vi"].ToString(),
                    user_name = odr["user_name"].ToString(),
                    ho_ten = odr["ho_ten"].ToString(),
                    lan_thi = odr["lan_thi"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    ngay_thi = odr.IsDBNull(ngayIndex) ? null : odr.GetDateTime(ngayIndex).ToString("dd/MM/yyyy"),
                    cau_dung = odr["dung"].ToString(),
                    tong_so_cau = odr["tong"].ToString(),
                    ten_de_thi = odr["name"].ToString()
                });
            }
            General.conn_global.Close();
            return bt;
        }

        
    }
    public class BaiThi_CTModel
    {
        public string cau_hoi_id { get; set; }
        public string cau_tl_id { get; set; }
        public string ten_cau_tl { get; set; }
        public string ten_cau_hoi { get; set; }
        public string user_status { get; set; }
        public string trang_thai { get; set; }

        public List<BaiThi_CTModel> getKQ_CT(string user_id, string lan_thi,string de_thi_id)
        {
            List<BaiThi_CTModel> BaiThi_CT = new List<BaiThi_CTModel>();
            //string sql = "";            
            //sql+= "select b.name ten_cau_hoi,c.ID cau_tl_id,c.NAME ten_cau_tl,c.STATUS,c.CAU_HOI_ID,a.trang_thai,d.user_status from";
            //sql+= "(";
            //sql+= "    select a.*,b.tong_2,decode(tong_1,tong_2,'dung','sai') trang_thai from";
            //sql+= "    (";
            //sql+= "        select ngay,cau_hoi_id, count(*) tong_1 from";
            //sql+= "        (";
            //sql+= "            select a.*,b.cau_hoi_id,b.status from";
            //sql+= "            (SELECT * FROM bai_thi a where user_id="+user_id+" and lan_thi="+lan_thi+" and de_thi_id="+de_thi_id+")a,";
            //sql+= "            (select * from cau_traloi) b";
            //sql+= "            where b.id=a.cau_tl_id and a.user_status=b.status";
            //sql+= "        ) group by ngay,cau_hoi_id";
            //sql+= "    )a,";
            //sql+= "    (select cau_hoi_id,count(*) tong_2 from cau_traloi group by cau_hoi_id) b";
            //sql+= "    where a.cau_hoi_id=b.cau_hoi_id";
            //sql+= ") a,";
            //sql+= "(select * from cau_hoi) b,";
            //sql+= "(select * from cau_traloi) c,";
            //sql+= "(select * from bai_thi) d";
            //sql+= " where a.cau_hoi_id=b.id and c.cau_hoi_id=b.id and d.cau_tl_id=c.id";
            //sql += " and d.user_id=" + user_id + " and d.lan_thi=" + lan_thi + " and d.de_thi_id=" + de_thi_id + " order by c.cau_hoi_id";


            string sql = "";
            sql += "select b.name ten_cau_hoi,c.ID cau_tl_id,c.NAME ten_cau_tl,c.STATUS,c.CAU_HOI_ID,a.trang_thai,d.user_status from";
            sql += "(";
            sql += "    select a.*,b.tong_2,decode(tong_1,tong_2,'dung','sai') trang_thai from";
            sql += "    (";
            sql += "        select ngay,cau_hoi_id, count(*) tong_1 from";
            sql += "        (";
            sql += "            select a.*,b.cau_hoi_id,b.status from";
            sql += "            (SELECT  a.user_id, a.lan_thi, a.de_thi_id,a.ngay,b.* FROM bai_thi a,table(a.ket_qua) b where user_id=" + user_id + " and lan_thi=" + lan_thi + " and de_thi_id=" + de_thi_id + ")a,";
            sql += "            (select * from cau_traloi) b";
            sql += "            where b.id=a.cau_tl_id and a.user_status=b.status";
            sql += "        ) group by ngay,cau_hoi_id";
            sql += "    )a,";
            sql += "    (select cau_hoi_id,count(*) tong_2 from cau_traloi group by cau_hoi_id) b";
            sql += "    where a.cau_hoi_id=b.cau_hoi_id";
            sql += ") a,";
            sql += "(select * from cau_hoi) b,";
            sql += "(select * from cau_traloi) c,";
            sql += "(SELECT  a.user_id, a.lan_thi, a.de_thi_id,a.ngay,b.* FROM bai_thi a,table(a.ket_qua) b where user_id=" + user_id + " and lan_thi=" + lan_thi + " and de_thi_id=" + de_thi_id + ") d";
            sql += " where a.cau_hoi_id=b.id and c.cau_hoi_id=b.id and d.cau_tl_id=c.id";
            sql += " and d.user_id=" + user_id + " and d.lan_thi=" + lan_thi + " and d.de_thi_id=" + de_thi_id + " order by c.cau_hoi_id,c.vi_tri";

            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                BaiThi_CT.Add(new BaiThi_CTModel
                {
                    cau_hoi_id = odr["cau_hoi_id"].ToString(),
                    cau_tl_id = odr["cau_tl_id"].ToString(),
                    ten_cau_tl = odr["ten_cau_tl"].ToString(),
                    ten_cau_hoi = odr["ten_cau_hoi"].ToString(),
                    user_status = odr["user_status"].ToString(),
                    trang_thai = odr["trang_thai"].ToString()
                });
            }
            General.conn_global.Close();
            return BaiThi_CT;
        }
    }    
}